<?php
return array
(
    'new_user' => 'SELECT COUNT(DISTINCT user_id) FROM log_create_user WHERE created_date = :date',
    'new_male' => 'SELECT COUNT(DISTINCT user_id) FROM log_create_user WHERE created_date = :date AND gender_id = :gender_male',
    'new_female' => 'SELECT COUNT(DISTINCT user_id) FROM log_create_user WHERE created_date = :date AND gender_id = :gender_female',
    'new_user_web' => 'SELECT COUNT(DISTINCT user_id) FROM log_create_user WHERE created_date = :date AND platform_id = :platform_web',
    'new_user_ios' => 'SELECT COUNT(DISTINCT user_id) FROM log_create_user WHERE created_date = :date AND platform_id = :platform_ios',
    'new_user_android' => 'SELECT COUNT(DISTINCT user_id) FROM log_create_user WHERE created_date = :date AND platform_id = :platform_android',

    'a1_total' => 'SET @date := :date, @n := 1; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date',
    'a1_web' => 'SET @date := :date, @n := 1, @platform_id := :platform_web; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND platform_id = :platform_web',
    'a1_ios' => 'SET @date := :date, @n := 1, @platform_id := :platform_ios; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND platform_id = :platform_ios',
    'a1_android' => 'SET @date := :date, @n := 1, @platform_id := :platform_android; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND platform_id = :platform_android',

    'a7_total' => 'SET @date := :date, @n := 7; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date',
    'a7_web' => 'SET @date := :date, @n := 7, @platform_id := :platform_web; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND platform_id = :platform_web',
    'a7_ios' => 'SET @date := :date, @n := 7, @platform_id := :platform_ios; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND platform_id = :platform_ios',
    'a7_android' => 'SET @date := :date, @n := 7, @platform_id := :platform_android; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND platform_id = :platform_android',

    'a30_total' => 'SET @date := :date, @n := 30; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date',
    'a30_web' => 'SET @date := :date, @n := 30, @platform_id := :platform_web; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND platform_id = :platform_web',
    'a30_ios' => 'SET @date := :date, @n := 30, @platform_id := :platform_ios; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND platform_id = :platform_ios',
    'a30_android' => 'SET @date := :date, @n := 30, @platform_id := :platform_android; SELECT COUNT(DISTINCT user_id) FROM log_login WHERE created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND platform_id = :platform_android',

    'r30_total' => 'SET @date := :date, @n := 30; SELECT COUNT(DISTINCT l.user_id) FROM log_login l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date)',
    'r30_web' => 'SET @date := :date, @n := 30, @platform_id := :platform_web; SELECT COUNT(DISTINCT l.user_id) FROM log_login l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND l.platform_id = @platform_id AND EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND l2.platform_id = @platform_id)',
    'r30_ios' => 'SET @date := :date, @n := 30, @platform_id := :platform_ios; SELECT COUNT(DISTINCT l.user_id) FROM log_login l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND l.platform_id = @platform_id AND EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND l2.platform_id = @platform_id)',
    'r30_android' => 'SET @date := :date, @n := 30, @platform_id := :platform_android; SELECT COUNT(DISTINCT l.user_id) FROM log_login l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND l.platform_id = @platform_id AND EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND l2.platform_id = @platform_id)',

    'c30_total' => 'SET @date := :date, @n := 30; SELECT COUNT(DISTINCT l.user_id) FROM log_login l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND NOT EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date)',
    'c30_web' => 'SET @date := :date, @n := 30, @platform_id := :platform_web; SELECT COUNT(DISTINCT l.user_id) FROM log_login l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND l.platform_id = @platform_id AND NOT EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND l2.platform_id = @platform_id)',
    'c30_ios' => 'SET @date := :date, @n := 30, @platform_id := :platform_ios; SELECT COUNT(DISTINCT l.user_id) FROM log_login l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND l.platform_id = @platform_id AND NOT EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND l2.platform_id = @platform_id)',
    'c30_android' => 'SET @date := :date, @n := 30, @platform_id := :platform_android; SELECT COUNT(DISTINCT l.user_id) FROM log_login l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND l.platform_id = @platform_id AND NOT EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND l2.platform_id = @platform_id)',

    'rt30_total' => 'SET @date := :date, @n := 30; SELECT COUNT(DISTINCT l.user_id) FROM log_create_user l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date)',
    'rt30_web' => 'SET @date := :date, @n := 30, @platform_id := :platform_web; SELECT COUNT(DISTINCT l.user_id) FROM log_create_user l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND l.platform_id = @platform_id AND EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND l2.platform_id = @platform_id)',
    'rt30_ios' => 'SET @date := :date, @n := 30, @platform_id := :platform_ios; SELECT COUNT(DISTINCT l.user_id) FROM log_create_user l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND l.platform_id = @platform_id AND EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND l2.platform_id = @platform_id)',
    'rt30_android' => 'SET @date := :date, @n := 30, @platform_id := :platform_android; SELECT COUNT(DISTINCT l.user_id) FROM log_create_user l WHERE l.created_date = DATE_SUB(@date, INTERVAL @n DAY) AND l.platform_id = @platform_id AND EXISTS (SELECT 1 FROM log_login l2 WHERE l.user_id = l2.user_id AND l2.created_date BETWEEN DATE_SUB(@date, INTERVAL @n-1 DAY) AND @date AND l2.platform_id = @platform_id)',

    'verification' => 'SELECT COUNT(DISTINCT user_id) FROM log_verify_user WHERE created_date = :date',
    'skip_verification' => 'SELECT COUNT(DISTINCT user_id) FROM log_skip_verify_user WHERE created_date = :date',

    'snapshot_total' => 'SELECT COUNT(*) FROM log_like_snapshot WHERE created_date = :date',
    'snapshot_total_user' => 'SELECT COUNT(DISTINCT user_id) FROM log_like_snapshot WHERE created_date = :date',
    'snapshot_like' => 'SELECT COUNT(*) FROM log_like_snapshot WHERE created_date = :date AND `like` = 1',
    'snapshot_like_user' => 'SELECT COUNT(DISTINCT user_id) FROM log_like_snapshot WHERE created_date = :date AND `like` = 1',
    'snapshot_dislike' => 'SELECT COUNT(*) FROM log_like_snapshot WHERE created_date = :date AND `like` = 0',
    'snapshot_dislike_user' => 'SELECT COUNT(DISTINCT user_id) FROM log_like_snapshot WHERE created_date = :date AND `like` = 0',
    'snapshot_mutual_match' => 'SELECT COUNT(*) FROM log_like_snapshot t1 JOIN log_like_snapshot t2 ON t1.user_id = t2.target_id AND t1.target_id = t2.user_id AND t2.`like` = 1 WHERE t1.created_date = :date AND t1.`like` = 1',

    'search' => 'SELECT COUNT(*) FROM log_search WHERE created_date = :date',
    'search_user' => 'SELECT COUNT(DISTINCT user_id) FROM log_search WHERE created_date = :date',

    'send_gift' => 'SELECT COUNT(*) FROM log_send_gift WHERE created_date = :date',
    'send_gift_user' => 'SELECT COUNT(DISTINCT user_id) FROM log_send_gift WHERE created_date = :date',

    'send_sticker' => 'SELECT COUNT(*) FROM log_send_sticker WHERE created_date = :date',
    'send_sticker_user' => 'SELECT COUNT(DISTINCT user_id) FROM log_send_sticker WHERE created_date = :date',

    'chat' => 'SELECT COUNT(*) FROM log_chat WHERE created_date = :date',
    'chat_user' => 'SELECT COUNT(DISTINCT user_id) FROM log_chat WHERE created_date = :date',

    'invite' => 'SELECT COUNT(*) FROM log_invite WHERE created_date = :date',
    'invite_user' => 'SELECT COUNT(DISTINCT user_id) FROM log_invite WHERE created_date = :date',

    'view_profile' => 'SELECT COUNT(*) FROM log_view_profile WHERE created_date = :date',
    'view_profile_user' => 'SELECT COUNT(DISTINCT user_id) FROM log_view_profile WHERE created_date = :date',

    'upload_photo' => 'SELECT COUNT(*) FROM log_upload_photo WHERE created_date = :date',
    'upload_photo_user' => 'SELECT COUNT(DISTINCT user_id) FROM log_upload_photo WHERE created_date = :date',
);
